/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mytools;

import java.sql.*;
import java.util.*;

/**
 *
 * @author xiaoxiao
 */
public class questionDAO {

    private Connection con;

    public questionDAO() throws Exception {
    }
    /**/

    public List<String> getquestionsubject() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct subject from subjectiveques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("subject"));
        }
        Collections.sort(list);
        con.close();
        return list;
    }

    public List<String> getquestionchapter() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct chapter from subjectiveques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("chapter"));
        }
        Collections.sort(list);
        con.close();
        return list;
    }

    public int getsctlquestionAmount(String subject, String chapter, String type, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from subjectiveques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%') and type like('%" + type + "%') and level like('%" + level + "%')";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    /*获取指定页的记录*/
    public List<question> getsctlquestionlist(int pagenumber, int pagesize, String subject, String chapter, String type, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        List<question> list = new ArrayList<question>();
        String sql = "select * from subjectiveques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%') and type like('%" + type + "%') and level like('%" + level + "%') ORDER BY id DESC  limit ?,?";
        PreparedStatement ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            question question = new question();
            question.setId(rs.getInt("id"));
            question.setName(rs.getString("name"));
            question.setAnswer(rs.getString("answer"));
            question.setType(rs.getString("type"));
            question.setLevel(rs.getString("level"));
            question.setSubject(rs.getString("subject"));
            question.setChapter(rs.getString("chapter"));
            question.setChapterinfo(rs.getString("chapterinfo"));
            list.add(question);
        }
        rs.close();
        con.close();
        return list;
    }

    public question getquestion(int id) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        question question = null;
        String sql = "select * from subjectiveques where id=?";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setInt(1, id);
        ResultSet rs = psmt.executeQuery();
        if (rs.next()) {
            question = new question();
            question.setId(id);
            question.setName(rs.getString("name"));
            question.setAnswer(rs.getString("answer"));
            question.setChapter(rs.getString("chapter"));
            question.setChapterinfo(rs.getString("chapterinfo"));
            question.setLevel(rs.getString("level"));
            question.setSubject(rs.getString("subject"));
            question.setType(rs.getString("type"));
        }
        con.close();
        return question;
    }

    public int deletequestion(int id) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        Statement st = con.createStatement();
        i = st.executeUpdate("delete from subjectiveques where id='" + id + "'");
        con.close();
        return i;
    }

    public int editquestion(question question) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "update subjectiveques set name=?,answer=?,type=?,level=?,subject=?,chapter=?,chapterinfo=? where id=?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ps.setString(1, question.getName());
        ps.setString(2, question.getAnswer());
        ps.setString(3, question.getType());
        ps.setString(4, question.getLevel());
        ps.setString(5, question.getSubject());
        ps.setString(6, question.getChapter());
        ps.setString(7, question.getChapterinfo());
        ps.setInt(8, question.getId());
        i = ps.executeUpdate();
        con.close();
        return i;
    }

    public int addquestion(question question) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "insert into subjectiveques value(null,?,?,?,?,?,?,?)";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setString(1, question.getName());
        psmt.setString(2, question.getAnswer());
        psmt.setString(3, question.getType());
        psmt.setString(4, question.getLevel());
        psmt.setString(5, question.getSubject());
        psmt.setString(6, question.getChapter());
        psmt.setString(7, question.getChapterinfo());
        i = psmt.executeUpdate();
        con.close();
        return i;

    }

    public int getquestionAmount() throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from subjectiveques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<question> getquestionlist(int pagenumber, int pagesize) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        List<question> list = new ArrayList<question>();
        String sql = "select * from subjectiveques limit ?,?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            question question = new question();
            question.setId(rs.getInt("id"));
            question.setName(rs.getString("name"));
            question.setAnswer(rs.getString("answer"));
            question.setType(rs.getString("type"));
            question.setLevel(rs.getString("level"));
            question.setSubject(rs.getString("subject"));
            question.setChapter(rs.getString("chapter"));
            question.setChapterinfo(rs.getString("chapterinfo"));
            list.add(question);
        }
        rs.close();
        con.close();
        return list;
    }

    public List<question> questionlist() throws SQLException, Exception {
        this.con = dbcon.getConnection();
        List<question> list = new ArrayList<question>();
        String sql = "select * from subjectiveques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            question question = new question();
            question.setId(rs.getInt("id"));
            question.setName(rs.getString("name"));
            question.setAnswer(rs.getString("answer"));
            question.setType(rs.getString("type"));
            question.setLevel(rs.getString("level"));
            question.setSubject(rs.getString("subject"));
            question.setChapter(rs.getString("chapter"));
            question.setChapterinfo(rs.getString("chapterinfo"));
            list.add(question);
        }
        rs.close();
        con.close();
        return list;
    }
}
